/*
 * To change this template, choose Tools | Templates and open the template in
 * the editor.
 */
package control;

import java.io.IOException;
import java.io.PrintWriter;
import java.math.BigInteger;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import model.*;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;

/**
 *
 * @author Vince
 */
public class MenuController extends HttpServlet {

    /**
     * Processes requests for both HTTP
     * <code>GET</code> and
     * <code>POST</code> methods.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        String getCategoryNameSQL = "SELECT C.category_name FROM Category C";
        String getCourseNameSQL = "SELECT C.cName FROM Course C";
        String getFileLocationSQL = "SELECT F.fName FROM File F";
        String redirection = request.getParameter("redirection");
        HttpSession sessie = request.getSession();
        SessionFactory sessionFactory = SchoolContentManager.sessionFactory;
        Session session = sessionFactory.getCurrentSession();
        String reddirect = "index";
        Transaction transaction = null;
        PrintWriter out = response.getWriter();


        if (redirection.equals("logout")) {
            response.sendRedirect("LogoutController");
        } else {
            if (redirection.equals("home")) {
                transaction = session.beginTransaction();
                Page page = (Page) session.get(Page.class, "0");
                transaction.commit();
                sessie.setAttribute("title", page.getTitle());
                sessie.setAttribute("subtitle", page.getSubtitle());
                sessie.setAttribute("message", page.getMessage());
                addHistori(sessie, sessie.getAttribute("contentPage"));
                sessie.setAttribute("contentPage", "/homepage.jsp");
            } else if (redirection.equals("register")) {
                addHistori(sessie, sessie.getAttribute("contentPage"));
                sessie.setAttribute("contentPage", "/register.jsp");
            } else if (redirection.equals("terug")) {
                sessie.setAttribute("contentPage", sessie.getAttribute("lastpage"));
            } else if (redirection.equals("viewFiles")) {
                addHistori(sessie, sessie.getAttribute("contentPage"));
                sessie.setAttribute("contentPage", "/viewFiles.jsp");
            }  else if (redirection.equals("uploadPage")) {
                transaction = session.beginTransaction();
//                ArrayList<UploadedFile> files = (ArrayList) session.createSQLQuery(getFileNameSQL).list();
                transaction.commit();
                sessie.removeAttribute("createFileError");
                if (sessie.getAttribute("isTeacher").equals(true)) {
                    sessie.setAttribute("isAnAnswer", false);
                } else {
                    sessie.setAttribute("isAnAnswer", true);
                }
                sessie.setAttribute("uploadPage", "createFile");
                addHistori(sessie, sessie.getAttribute("contentPage"));
                sessie.setAttribute("contentPage", "/uploadPage.jsp");
            } else if (redirection.equals("createCategory")) {
                sessie.removeAttribute("createCategoryError");
                sessie.setAttribute("courseManager", "createCategory");
                addHistori(sessie, sessie.getAttribute("contentPage"));
                sessie.setAttribute("contentPage", "/createCategory.jsp");
            } else if (redirection.equals("createCourse")) {
                transaction = session.beginTransaction();
                ArrayList<Category> categories = (ArrayList) session.createSQLQuery(getCategoryNameSQL).list();
                String getTeachersSQL = "SELECT * FROM Gebruiker G WHERE G.ucode IN (SELECT T.ucode FROM teacher T)";
                ArrayList<Object[]> teachers = (ArrayList) session.createSQLQuery(getTeachersSQL).list();
                transaction.commit();
                sessie.removeAttribute("createCourseError");
                sessie.setAttribute("categories", categories);
                sessie.setAttribute("teachers", teachers);
                sessie.setAttribute("courseManager", "createCourse");
                addHistori(sessie, sessie.getAttribute("contentPage"));
                sessie.setAttribute("contentPage", "/createCourse.jsp");
            } else if (redirection.equals("deleteCategory")) {
                transaction = session.beginTransaction();
                ArrayList<Category> categories = (ArrayList) session.createSQLQuery(getCategoryNameSQL).list();
                transaction.commit();
                sessie.removeAttribute("deleteCategoryMessage");
                sessie.setAttribute("categories", categories);
                sessie.setAttribute("courseManager", "deleteCategory");
                addHistori(sessie, sessie.getAttribute("contentPage"));
                sessie.setAttribute("contentPage", "/deleteCategory.jsp");
            } else if (redirection.equals("classroom")) {
                addHistori(sessie, sessie.getAttribute("contentPage"));
                sessie.setAttribute("contentPage", "VirtualClassroom");
            } else if (redirection.equals("deleteCourse")) {
                transaction = session.beginTransaction();
                ArrayList<Course> courses = (ArrayList) session.createSQLQuery(getCourseNameSQL).list();
                transaction.commit();
                sessie.removeAttribute("deleteCourseMessage");
                sessie.setAttribute("courses", courses);
                sessie.setAttribute("courseManager", "deleteCourse");
                addHistori(sessie, sessie.getAttribute("contentPage"));
                sessie.setAttribute("contentPage", "/deleteCourse.jsp");
            } else if (redirection.equals("editCategorySelect")) {
                transaction = session.beginTransaction();
                ArrayList<Category> categories = (ArrayList) session.createSQLQuery(getCategoryNameSQL).list();
                transaction.commit();
                sessie.removeAttribute("editCategorySelectMessage");
                sessie.setAttribute("categories", categories);
                sessie.setAttribute("courseManager", "editCategorySelect");
                addHistori(sessie, sessie.getAttribute("contentPage"));
                sessie.setAttribute("contentPage", "/editCategorySelect.jsp");
            } else if (redirection.equals("editCourseSelect")) {
                transaction = session.beginTransaction();
                ArrayList<Course> courses = (ArrayList) session.createSQLQuery(getCourseNameSQL).list();
                transaction.commit();
                sessie.removeAttribute("editCategorySelectMessage");
                sessie.setAttribute("courses", courses);
                sessie.setAttribute("courseManager", "editCourseSelect");
                addHistori(sessie, sessie.getAttribute("contentPage"));
                sessie.setAttribute("contentPage", "/editCourseSelect.jsp");
            } else if (redirection.equals("selectCourse")) {
                transaction = session.beginTransaction();
                ArrayList<String> categories = (ArrayList) session.createSQLQuery(getCategoryNameSQL).list();
                String courses = "";
                int index=0;
                for (int i = 0; i < categories.size(); i++) {
                    courses += "<tr ><td colspan='2'>\n<h3>" + categories.get(i) + "</h3>\n</td></tr>";
                    ArrayList<String> coursesl = (ArrayList) session.createSQLQuery("SELECT C.cname FROM Course C WHERE C.category_category_name='" + categories.get(i) + "'").list();
                    for (int j = 0; j < coursesl.size(); j++) {
                        
                        if (((ArrayList) session.createSQLQuery("SELECT p.paydate FROM payment p WHERE (p.ucode='" + ((Gebruiker) sessie.getAttribute("cuser")).getuCode() + "' AND p.cname='" + coursesl.get(j) + "' )").list()).size() + ((ArrayList) session.createSQLQuery("SELECT C.cname FROM course C WHERE C.teacher_ucode='" + ((Gebruiker) sessie.getAttribute("cuser")).getuCode() + "' AND C.cname='" + coursesl.get(j) + "'").list()).size() == 0) {
                            courses += "<tr id='z"+index+"' >\n<td >" + coursesl.get(j) + "</td><td class='table-noborder'><button onclick='window.location=\"Menu?redirection=subscribe&cname=" + coursesl.get(j) + "\";' value='subscribe'>subscribe</button></td>\n</tr>";
                        } else {
                            courses += "<tr id='z"+index+"' colspan='2' >\n<td ><A HREF='Menu?redirection=viewCourse&name=" + coursesl.get(j) + "'>" + coursesl.get(j) + "</A></td>\n</tr>";
                        }
                        index++;
                    }
                    courses += "\n";
                }
                courses += "<tr class='table-noborder'><td class='table-noborder' colspan='2'>\n<h3>your courses</h3>\n<ul></td></tr>";
                ArrayList<String> coursesl = (ArrayList) session.createSQLQuery("SELECT p.cname FROM payment p WHERE p.ucode='" + ((Gebruiker) sessie.getAttribute("cuser")).getuCode() + "'").list();
                coursesl.addAll((ArrayList) session.createSQLQuery("SELECT C.cname FROM course C WHERE C.teacher_ucode='" + ((Gebruiker) sessie.getAttribute("cuser")).getuCode() + "'").list());
                removeDuplicateWithOrder(coursesl);
                for (int j = 0; j < coursesl.size(); j++) {
                    
                    courses += "<tr id='z"+index+"' class='table-noborder'>\n<td class='table-noborder' colspan='2'><A HREF='Menu?redirection=viewCourse&name=" + coursesl.get(j) + "'>" + coursesl.get(j) + "</A></td>\n</tr>";
                    index++;
                }
                courses += "\n";
                System.out.println("123456781234567fsddfdsfsgfdgsfgsdfgsdfgsgsfdgsdfgsdfgdfg834567845678--" + courses);
                sessie.removeAttribute("selectCourseCategoryMessage");
                sessie.setAttribute("courses", courses);
                sessie.setAttribute("lenght", index);
                transaction.commit();
                addHistori(sessie, sessie.getAttribute("contentPage"));
                sessie.setAttribute("contentPage", "/courseCatalogue.jsp");
            } else if (redirection.equals("courseManager")) {
                
                addHistori(sessie, sessie.getAttribute("contentPage"));
                transaction = session.beginTransaction();
                ArrayList<String> categories = (ArrayList) session.createSQLQuery(getCategoryNameSQL).list();
                String courses = "";
                int index = 0;
                for (int i = 0; i < categories.size(); i++) {
                    courses += "<tr id='z"+index+"'><td ><b>" + categories.get(i) + "</b></td><td>\n<form action='CourseManager?p=editCategorySelect' method='post' name='editCategorySelectForm'><input type='hidden' name='category' value='"+categories.get(i) + "'></input><input type='submit' value='edit'></form></td><td>\n<form action='CourseManager?p=deleteCategory' method='post' name='editCategorySelectForm'><input type='hidden' name='category' value='"+categories.get(i) + "'></input><input type='submit' value='delete'></form></td></tr>";
                    
                    index++;
                    ArrayList<String> coursesl = (ArrayList) session.createSQLQuery("SELECT C.cname FROM Course C WHERE C.category_category_name='" + categories.get(i) + "'").list();
                    for (int j = 0; j < coursesl.size(); j++) {

                            courses += "<tr id='z"+index+"'><td>&nbsp;&nbsp;&nbsp;&nbsp;" + coursesl.get(j) + "</td><td>\n<form action='CourseManager?p=editCourseSelect' method='post' name='editCourseSelectForm'><input type='hidden' name='courses' value='"+coursesl.get(j) + "'></input><input type='submit' value='edit'></form></td><td>\n<form action='CourseManager?p=deleteCourse' method='post' name='editCategoSelectForm'><input type='hidden' name='course' value='"+coursesl.get(j) + "'></input><input type='submit' value='delete'></form></td></tr>";
                            //courses += "<tr id='z" + index + "'>\n<td>"+coursesl.get(j) +"</td>\n</tr>";
                        
                        index++;
                    }
                    courses += "\n";
                }
                
                transaction.commit();
                
                sessie.setAttribute("blabla", courses);
                sessie.setAttribute("lenght", index);
                sessie.setAttribute("contentPage", "/courseManager.jsp");
            } else if (redirection.equals("back")) {
                sessie.setAttribute("contentPage", getLast(sessie));
            } else if (redirection.equals("send announcement")) {
                addHistori(sessie, sessie.getAttribute("contentPage"));
                sessie.setAttribute("contentPage", "/sendnot.jsp?re= ");
            } else if (redirection.equals("viewSubscribed")) {
                String SQL = "SELECT * FROM gebruiker G WHERE G.ucode IN (SELECT P.ucode FROM payment P WHERE P.cname='" + ((Course) sessie.getAttribute("course")).getcName() + "') ORDER BY G.lastname";
                transaction = session.beginTransaction();
                ArrayList<Object[]> students = (ArrayList) session.createSQLQuery(SQL).list();
                transaction.commit();
                
                sessie.setAttribute("Cn", ((Course) sessie.getAttribute("course")).getcName());
                sessie.setAttribute("subscribedStud", students);
                addHistori(sessie, sessie.getAttribute("contentPage"));
                sessie.setAttribute("contentPage", "/viewSubscribedStudends.jsp");
            } else if (redirection.equals("sendMail")) {
                addHistori(sessie, sessie.getAttribute("contentPage"));
                sessie.setAttribute("to", request.getParameter("to"));
                sessie.setAttribute("contentPage", "/sendMail.jsp?re= ");
            } else if (redirection.equals("viewCourse")) {
                addHistori(sessie, sessie.getAttribute("contentPage"));
                transaction = session.beginTransaction();
                String name = request.getParameter("name").replace("%20", " ");
                Course course = (Course) session.get(Course.class, name);
                sessie.setAttribute("course", course);
                sessie.setAttribute("courseTitle", course.getcName());
                sessie.setAttribute("courseDescription", course.getcDescription());
                sessie.setAttribute("courseTeacher", course.getTeacher().getName());
                ArrayList<Integer> exercises = (ArrayList) session.createSQLQuery("SELECT E.exercise_id FROM exercise E WHERE E.course_cname='" + course.getcName() + "' ORDER BY E.volgnr").list();
                String filesByCategory;
                if (sessie.getAttribute("isTeacher") != null && sessie.getAttribute("isTeacher").equals(true)) {
                    filesByCategory = "<table class='noborder'><tr><td><h3>exercises:</h3></td><td><button onclick='window.location=\"Menu?redirection=addexercise\";'>add</button></td></tr>";
                    for (int i = 0; i < exercises.size(); i++) {
                        String id = exercises.get(i).toString();
                        filesByCategory += "<tr><td colspan='2'><a HREF='Menu?redirection=viewExercise&id=" + (id) + "'>" + ((Exercise) session.get(Exercise.class, exercises.get(i))).getName() + "</a></td>";

                    }
                } else {
                    filesByCategory = "<table class='noborder'><tr><td><h3>exercises:</h3></td></tr>";
                    for (int i = 0; i < exercises.size(); i++) {
                        String id = exercises.get(i).toString();
                        filesByCategory += "<tr><td><a HREF='Menu?redirection=viewExercise&id=" + (id) + "'>" + ((Exercise) session.get(Exercise.class, exercises.get(i))).getName() + "</a></td>";

                    }

                }

                filesByCategory += "</table><br>";
                //filesByCategory += "<li>\n<h3>Available files</h3>\n<ul>";
                System.out.println("files: " + filesByCategory);
                sessie.setAttribute("filesByCategory", filesByCategory);
                System.out.println("----\n\n\n\n\n" + course.getcDescription() + "\n\n\n\n\n\n");
                sessie.setAttribute("contentPage", "/viewCourse.jsp");
            } else if (redirection.equals("viewExercise")) {
                addHistori(sessie, sessie.getAttribute("contentPage"));
                transaction = session.beginTransaction();
                int id = Integer.parseInt(request.getParameter("id"));
                Exercise exercise = (Exercise) session.get(Exercise.class, id);
                sessie.setAttribute("exercise", exercise);
                sessie.setAttribute("exerciseTitle", exercise.getId());
                sessie.setAttribute("exerciseName", exercise.getName());
                sessie.setAttribute("exerciseDescription", exercise.getDescription());
                ArrayList<String> files = (ArrayList) session.createSQLQuery(getFileLocationSQL).list();
                String filesByCategory = "<h3>Files:</h3><table class='noborder'>";
                String answerByCategory = "<h3>Submitted answers:</h3><table class='noborder'>";
                for (int i = 0; i < files.size(); i++) {
                    //filesByCategory += "<li>\n" + files.get(i) + "\n<ul>";
                    //filesByCategory += "<li>\n<ul>";
                    ArrayList<String> filesql = (ArrayList) session.createSQLQuery("SELECT F.fLocation FROM File F WHERE F.fName='" + files.get(i) + "' AND F.exercise_exercise_id='" + exercise.getId() + "' AND F.isAnswer=false").list();
                    ArrayList<Integer> fileidsql = (ArrayList) session.createSQLQuery("SELECT F.file_id FROM File F WHERE F.fName='" + files.get(i) + "' AND F.exercise_exercise_id='" + exercise.getId() + "' AND F.isAnswer=false").list();
                    ArrayList<String> answersql = (ArrayList) session.createSQLQuery("SELECT F.fLocation FROM File F WHERE F.fName='" + files.get(i) + "' AND F.exercise_exercise_id='" + exercise.getId() + "' AND F.isAnswer=true").list();
                    ArrayList<String> answeridsql = (ArrayList) session.createSQLQuery("SELECT F.file_id FROM File F WHERE F.fName='" + files.get(i) + "' AND F.exercise_exercise_id='" + exercise.getId() + "' AND F.isAnswer=true").list();
                    ArrayList<String> ownanswersql = (ArrayList) session.createSQLQuery("SELECT F.fLocation FROM File F WHERE F.fName='" + files.get(i) + "' AND F.exercise_exercise_id='" + exercise.getId() + "' AND F.isAnswer='true' AND F.ownername='"+((Gebruiker)sessie.getAttribute("cuser")).getuCode()+"'").list();
                    ArrayList<String> ownansweridsql = (ArrayList) session.createSQLQuery("SELECT F.file_id FROM File F WHERE F.fName='" + files.get(i) + "' AND F.exercise_exercise_id='" + exercise.getId() + "' AND F.isAnswer='true' AND F.ownername='"+((Gebruiker)sessie.getAttribute("cuser")).getuCode()+"'").list();
//                    ArrayList<String> ownanswersql = (ArrayList) session.createSQLQuery("SELECT F.fLocation FROM File F WHERE F.fName='" + files.get(i) + "' AND F.exercise_exercise_id='" + exercise.getId() + "''AND F.isAnswer='true'").list();
//                    ArrayList<String> ownansweridsql = (ArrayList) session.createSQLQuery("SELECT F.file_id FROM File F WHERE F.fName='" + files.get(i) + "' AND F.exercise_exercise_id='" + exercise.getId() + "'AND F.isAnswer='true'").list();
                    System.out.println("Size: " + answersql.size());
                    System.out.println("files.get: " + files.get(i));
                    if (sessie.getAttribute("isTeacher").equals(true)) {
                        if(files.size()>0&&answersql.size()>0){
                        for (int j = 0; j < filesql.size(); j++) {
                            //System.out.println("fileidsql: "+fileidsql.get(j));
                            //System.out.println("fileidsql J: "+fileidsql.get(j));
                            filesByCategory += "<tr><td><a HREF='" + answersql.get(j) + "'>" + files.get(i) + "</a></td>";
                            filesByCategory += "<td><button onclick='window.location=\"Menu?redirection=deleteFile&fLocation=" + (answeridsql.get(j)) + "&cname=" + exercise.getName() + "\";' value='deleteFile'>Delete</button></td>";
                            filesByCategory += "</tr>";
                        }
                        }
                    } else if (sessie.getAttribute("isTeacher").equals(false)) {
                        if(files.size()>0&&answersql.size()>0){
                        for (int j = 0; j < filesql.size(); j++) {
                            //System.out.println("fileidsql: "+fileidsql.get(j));
                            //System.out.println("fileidsql J: "+fileidsql.get(j));
                            filesByCategory += "<tr><td><a HREF='" + ownanswersql.get(j) + "'>" + files.get(i) + "</a></td>";
                            filesByCategory += "<td><button onclick='window.location=\"Menu?redirection=deleteFile&fLocation=" + (ownansweridsql.get(j)) + "&cname=" + exercise.getName() + "\";' value='deleteFile'>Delete</button></td>";
                            filesByCategory += "</tr>";
                        }
                        }
                   
                    }
                    for (int j = 0; j < filesql.size(); j++) {
                        //System.out.println("fileidsql: "+fileidsql.get(j));
                        //System.out.println("fileidsql J: "+fileidsql.get(j));
                        if (sessie.getAttribute("isTeacher").equals(true)) {

                            filesByCategory += "<tr><td><a HREF='" + filesql.get(j) + "'>" + files.get(i) + "</a></td>";
                            filesByCategory += "<td><button onclick='window.location=\"Menu?redirection=deleteFile&fLocation=" + (fileidsql.get(j)) + "&cname=" + exercise.getName() + "\";' value='deleteFile'>Delete</button></td>";
                        } else {
                            filesByCategory += "<tr><td colspan='2'><a HREF='" + filesql.get(j) + "'>" + files.get(i) + "</a></td>";
                        }
                        filesByCategory += "</tr>";
                    }
                    if (sessie.getAttribute("isTeacher").equals(true)) {
                        if(files.size()>0&&answersql.size()>0){
                        for (int j = 0; j < filesql.size(); j++) {
                            //System.out.println("fileidsql: "+fileidsql.get(j));
                            //System.out.println("fileidsql J: "+fileidsql.get(j));
                            answerByCategory += "<tr><td><a HREF='" + answersql.get(j) + "'>" + files.get(i) + "</a></td>";
                            answerByCategory += "<td><button onclick='window.location=\"Menu?redirection=deleteFile&fLocation=" + (answeridsql.get(j)) + "&cname=" + exercise.getName() + "\";' value='deleteFile'>Delete</button></td>";
                            answerByCategory += "</tr>";
                        }
                        }
                    }  
                    
                }

                filesByCategory += "</table><br>";
                answerByCategory+= "</table><br>";
                //if(sessie.getAttribute("isTeacher").equals(true)){
                    filesByCategory+=answerByCategory;
                //}
                //filesByCategory += "<li>\n<h3>Available files</h3>\n<ul>";
                System.out.println("files: " + filesByCategory);
                sessie.setAttribute("filesByCategory", filesByCategory);
                sessie.setAttribute("contentPage", "/viewExercise.jsp");
            } else if (redirection.equals("manageStudent")) {
                String sucodeSQL = "SELECT G.ucode FROM Gebruiker G WHERE G.ucode IN (SELECT S.ucode FROM Student S) order by G.ucode";
                String sfnameSQL = "SELECT G.firstname FROM Gebruiker G WHERE G.ucode IN (SELECT S.ucode FROM Student S) order by G.ucode";
                String sinsertSQL = "SELECT G.insertion FROM Gebruiker G WHERE G.ucode IN (SELECT S.ucode FROM Student S) order by G.ucode";
                String slnameSQL = "SELECT G.lastname FROM Gebruiker G WHERE G.ucode IN (SELECT S.ucode FROM Student S) order by G.ucode";

                transaction = session.beginTransaction();
                ArrayList<String> sucode = (ArrayList) session.createSQLQuery(sucodeSQL).list();
                ArrayList<String> sfname = (ArrayList) session.createSQLQuery(sfnameSQL).list();
                ArrayList<String> sinsert = (ArrayList) session.createSQLQuery(sinsertSQL).list();
                ArrayList<String> slname = (ArrayList) session.createSQLQuery(slnameSQL).list();
                transaction.commit();

                sessie.removeAttribute("msmsg");
                sessie.setAttribute("lenght", sucode.size());
                sessie.setAttribute("sucode", sucode);
                sessie.setAttribute("sfname", sfname);
                sessie.setAttribute("sinsert", sinsert);
                sessie.setAttribute("slname", slname);
                addHistori(sessie, sessie.getAttribute("contentPage"));
                sessie.setAttribute("contentPage", "/manageStudentSelect.jsp");
            } else if (redirection.equals("stopclassroom")) {
                transaction = session.beginTransaction();

                Classroom classroom = (Classroom) session.get(Classroom.class, ((Course) sessie.getAttribute("course")));

                classroom.setIsReady(false);

                session.saveOrUpdate(classroom);
                transaction.commit();
                reddirect = "closepage.jsp";
            } else if (redirection.equals("manageTeacher")) {
                String tucodeSQL = "SELECT G.ucode FROM Gebruiker G WHERE G.ucode IN (SELECT T.ucode FROM Teacher T) order by G.ucode";
                String tfnameSQL = "SELECT G.firstname FROM Gebruiker G WHERE G.ucode IN (SELECT T.ucode FROM Teacher T) order by G.ucode";
                String tinsertSQL = "SELECT G.insertion FROM Gebruiker G WHERE G.ucode IN (SELECT T.ucode FROM Teacher T) order by G.ucode";
                String tlnameSQL = "SELECT G.lastname FROM Gebruiker G WHERE G.ucode IN (SELECT T.ucode FROM Teacher T) order by G.ucode";

                transaction = session.beginTransaction();
                ArrayList<String> tucode = (ArrayList) session.createSQLQuery(tucodeSQL).list();
                ArrayList<String> tfname = (ArrayList) session.createSQLQuery(tfnameSQL).list();
                ArrayList<String> tinsert = (ArrayList) session.createSQLQuery(tinsertSQL).list();
                ArrayList<String> tlname = (ArrayList) session.createSQLQuery(tlnameSQL).list();
                transaction.commit();

                sessie.removeAttribute("etmsg");
                sessie.setAttribute("lenght", tucode.size());
                sessie.setAttribute("tucode", tucode);
                sessie.setAttribute("tfname", tfname);
                sessie.setAttribute("tinsert", tinsert);
                sessie.setAttribute("tlname", tlname);

                addHistori(sessie, sessie.getAttribute("contentPage"));
                sessie.setAttribute("contentPage", "/editTeacherSelect.jsp");
            } else if (redirection.equals("createTeacher")) {
                sessie.setAttribute("manageTeacherController", "createTeacher");
                addHistori(sessie, sessie.getAttribute("contentPage"));
                sessie.setAttribute("contentPage", "/createTeacher.jsp");
            } else if (redirection.equals("editTeacher")) {
                String tucodeSQL = "SELECT G.ucode FROM Gebruiker G WHERE G.ucode IN (SELECT T.ucode FROM Teacher T) order by G.ucode";
                String tfnameSQL = "SELECT G.firstname FROM Gebruiker G WHERE G.ucode IN (SELECT T.ucode FROM Teacher T) order by G.ucode";
                String tinsertSQL = "SELECT G.insertion FROM Gebruiker G WHERE G.ucode IN (SELECT T.ucode FROM Teacher T) order by G.ucode";
                String tlnameSQL = "SELECT G.lastname FROM Gebruiker G WHERE G.ucode IN (SELECT T.ucode FROM Teacher T) order by G.ucode";

                transaction = session.beginTransaction();
                ArrayList<String> tucode = (ArrayList) session.createSQLQuery(tucodeSQL).list();
                ArrayList<String> tfname = (ArrayList) session.createSQLQuery(tfnameSQL).list();
                ArrayList<String> tinsert = (ArrayList) session.createSQLQuery(tinsertSQL).list();
                ArrayList<String> tlname = (ArrayList) session.createSQLQuery(tlnameSQL).list();
                transaction.commit();

                sessie.removeAttribute("etmsg");
                sessie.setAttribute("lenght", tucode.size());
                sessie.setAttribute("tucode", tucode);
                sessie.setAttribute("tfname", tfname);
                sessie.setAttribute("tinsert", tinsert);
                sessie.setAttribute("tlname", tlname);

                addHistori(sessie, sessie.getAttribute("contentPage"));
                sessie.setAttribute("contentPage", "/editTeacherSelect.jsp");

            } else if (redirection.equals("subscribe")) {
                transaction = session.beginTransaction();
                String cname = request.getParameter("cname");
                String ucode = (String) sessie.getAttribute("user");
                String DATE_FORMAT = "yyyy-MM-dd";
                SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
                Calendar cal = Calendar.getInstance();
                String paydate = sdf.format(cal.getTime());

                Payment payment = new Payment(new PaymentPK(paydate, cname, ucode));
                session.saveOrUpdate(payment);
                transaction.commit();
                reddirect = "Menu?redirection=selectCourse";
            } else if (redirection.equals("deleteFile")) {
                transaction = session.beginTransaction();
                String floc = request.getParameter("fLocation");
                String bla = 10 + "";
                int fLocation = Integer.parseInt(request.getParameter("fLocation"));
                System.out.println("fLocation: " + fLocation);
                ArrayList<String> deleteFileSQL = (ArrayList) session.createSQLQuery("SELECT file_id FROM File F WHERE F.fLocation='" + fLocation + "'").list();
                System.out.println("deleteFileSQL: " + deleteFileSQL.toString());
                UploadedFile file = (UploadedFile) session.get(UploadedFile.class, fLocation);
                System.out.println("fileid: " + file.getId());
                session.delete(file);
                //ArrayList<String> deleteFileSQL = (ArrayList) session.createSQLQuery("DELETE FROM File F WHERE F.file_id='"+ file_id +"'").list();

                transaction.commit();
                reddirect = "Menu?redirection=viewExercise&id=" + sessie.getAttribute("exerciseTitle");
            } else if (redirection.equals("disclaimer")) {
                sessie.setAttribute("contentPage", "/disclaimer.jsp");
                addHistori(sessie, sessie.getAttribute("contentPage"));
            } else if (redirection.equals("about")) {
                sessie.setAttribute("contentPage", "/about.jsp");
                addHistori(sessie, sessie.getAttribute("contentPage"));
            } else if (redirection.equals("feedback")) {
                sessie.setAttribute("contentPage", "/feedback.jsp");
                addHistori(sessie, sessie.getAttribute("contentPage"));
            } else if (redirection.equals("sendFeedback")) {
                new MailController().mail(request.getParameter("text"), request.getParameter("subject"), "three2learn.2dev@gmail.com");

            } else if (redirection.equals("addexercise")) {

                transaction = session.beginTransaction();
                ArrayList<String> vnum = new ArrayList();
                ArrayList<String> exercises = (ArrayList) session.createSQLQuery("SELECT E.exercise_id FROM exercise E WHERE E.course_cname='" + sessie.getAttribute("courseTitle") + "'").list();
                for (int i = 0; i < exercises.size() + 1; i++) {
                    vnum.add(i + "");
                }
                sessie.setAttribute("vnum", vnum);
                addHistori(sessie, sessie.getAttribute("contentPage"));
                sessie.setAttribute("contentPage", "/newexercise.jsp");
            } else if (redirection.equals("manageHomepage")) {
                transaction = session.beginTransaction();
                Page page = (Page) session.get(Page.class, "0");
                transaction.commit();
                sessie.removeAttribute("hpmsg");
                sessie.setAttribute("title", page.getTitle());
                sessie.setAttribute("subtitle", page.getSubtitle());
                sessie.setAttribute("message", page.getMessage());
                sessie.setAttribute("contentPage", "/manageHomepage.jsp");
                addHistori(sessie, sessie.getAttribute("contentPage"));
            }
            response.sendRedirect(reddirect);
        }
        if (transaction != null && transaction.isActive()) {
            transaction.commit();
        }
        response.setContentType("text/html;charset=UTF-8");
    }

    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /** 
     * Handles the HTTP <code>GET</code> method.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /** 
     * Handles the HTTP <code>POST</code> method.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /** 
     * Returns a short description of the servlet.
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>

    private void addHistori(HttpSession sessie, Object attribute) {
        List<Object> historie;
        if (sessie.getAttribute("lastpage") == null) {
            historie = new ArrayList();
        } else {
            historie = (ArrayList<Object>) sessie.getAttribute("lastpage");
        }
        System.out.println("terug naar" + attribute);
        historie.add(attribute);
        sessie.setAttribute("lastpage", historie);
    }

    private Object getLast(HttpSession sessie) {
        List<Object> historie = (List<Object>) sessie.getAttribute("lastpage");
        Object re = historie.get(historie.size() - 1);
        historie.remove(re);
        return re;
    }

    public void removeDuplicateWithOrder(ArrayList arlList) {
        Set set = new HashSet();
        List newList = new ArrayList();
        for (Iterator iter = arlList.iterator(); iter.hasNext();) {
            Object element = iter.next();
            if (set.add(element)) {
                newList.add(element);
            }
        }
        arlList.clear();
        arlList.addAll(newList);
    }
}
